package MySQLConnection;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

import model.CalculationMethod;
import model.Criterion;
import model.Formula;
import model.Method;

import common.Enums.*;
import common.Exceptions.DAOException;
import common.Exceptions.FormulaException;
import common.Utils.LinkedSet;

import DAO.MethodDAO;

public class MySQLMethodDAO implements MethodDAO
{
	private final static String TABLE_NAME = MySqlDAOFactory.DBName + "." + "method"; 
	private static MySQLMethodDAO instance;
	
	public static MySQLMethodDAO getInstance()
	{
		if(instance == null)
			instance = new MySQLMethodDAO();
		
		return instance;
	}
	
	@Override
	public void insert(Method item) throws DAOException
	{
		String query = "INSERT INTO " + TABLE_NAME + " (met_id, met_name, met_reliability, met_type, met_createdDate, met_lastUpdated) VALUES(?, ?, ?, ?, ?, ?)";
		
		boolean lockOwner = false;

		try 
		{
			PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
			preStatement.setString(1, item.getID().toString());
			preStatement.setString(2, item.getName());
			preStatement.setInt(3, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.RELIABILITY_TYPE, item.getReliability().toString()));
			preStatement.setInt(4, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.METHOD_TYPE, item.getType().toString()));
			preStatement.setTimestamp(5, new java.sql.Timestamp(item.getCreatedDate().getTime()));
			preStatement.setTimestamp(6, new java.sql.Timestamp(item.getLastUpdated().getTime()));
			
			preStatement.executeUpdate();
			
			if(item.getType() == MethodType.CALCULATION)
			{
				lockOwner = MySqlDAOFactory.lockConnection.tryLock();
				CalculationMethod cm = (CalculationMethod)item;
				if(cm.getFormula() != null)
				{
					MySqlDAOFactory.getInstance().getFormulaDAO().insert(cm.getFormula());
					
					query = "INSERT INTO " + MySqlDAOFactory.DBName + "." + item.getClass().getSimpleName() + "(met_id, for_id) VALUES(?, ?);";
				
					preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
					preStatement.setString(1, item.getID().toString());
					preStatement.setString(2, cm.getFormula().getID().toString());
					
					preStatement.executeUpdate();
				}
			}
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The insert method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
	}
	
	@Override
	public void update(Method item) throws DAOException 
	{
		String query = "SELECT * FROM "+ TABLE_NAME + " WHERE met_id = '" + item.getID().toString() + "';";
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String updateQuery = "UPDATE " + TABLE_NAME + " set met_name =  ?, met_reliability = ?, met_lastUpdated = ? WHERE met_id = ?"; 
				
				PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(updateQuery);
				preStatement.setString(1, item.getName());
				preStatement.setInt(2, MySqlDAOFactory.getEnumIdByName(MySqlDAOFactory.RELIABILITY_TYPE, item.getReliability().toString()));
				preStatement.setTimestamp(3, new java.sql.Timestamp(new java.util.Date().getTime()));
				preStatement.setString(4, item.getID().toString());
				
				preStatement.executeUpdate();
			
				if(item.getType() == MethodType.CALCULATION)
				{
					lockOwner = MySqlDAOFactory.lockConnection.tryLock();
					CalculationMethod calMet = (CalculationMethod)item;
					if(calMet.getFormula() != null)
					{
						if(MySqlDAOFactory.getInstance().getFormulaDAO().getByID(calMet.getFormula().getID()) != null)
							MySqlDAOFactory.getInstance().getFormulaDAO().update(calMet.getFormula());
						else
						{
							MySqlDAOFactory.getInstance().getFormulaDAO().insert(calMet.getFormula());
							
							query = "INSERT INTO " + MySqlDAOFactory.DBName + "." + item.getClass().getSimpleName() + "(met_id, for_id) VALUES(?, ?);";
							
							preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
							preStatement.setString(1, item.getID().toString());
							preStatement.setString(2, calMet.getFormula().getID().toString());
							
							preStatement.executeUpdate();
						}
						
						LinkedSet<UUID> relatedMeasures = getRelatedDerivedMeasuresID(calMet.getID());
						for(Criterion derMeasure : calMet.getDerivedMeasures())
							if(!relatedMeasures.contains(derMeasure.getID()))
								addDerivedMeasure(calMet.getID(), derMeasure.getID());
						
						for(UUID toRemoveMeasure : findOldMeasures(relatedMeasures, calMet.getDerivedMeasures()))
							removeDerivedMeasure(toRemoveMeasure);
					}
				}
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. Sol_id = " + item.getID());
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + item.getID() + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The update method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
	}

	@Override
	public void delete(UUID id) throws DAOException 
	{
		String query = "SELECT * FROM " + TABLE_NAME + " WHERE met_id = '" + id + "';";

		try 
		{
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			if(dataSet.next())
			{
				String deleteQuery = "DELETE FROM " + TABLE_NAME + " WHERE met_id = '" + id + "'";
				
				statement = MySqlDAOFactory.getInstance().createStatement();
				if(statement.executeUpdate(deleteQuery) == 0)
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " couldn't be deleted due to an unknown error.");
					
				if(dataSet.next())
					System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. met_id = " + id);
			}
			else
				throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The delete method failed due to some Query error: " + e.getMessage(), e);
		}
		finally
		{
			MySqlDAOFactory.getInstance().closeConnection();
		}
	}

	@Override
	public List<Method> getAll() throws DAOException 
	{
		String query = "SELECT met_id FROM " + TABLE_NAME + ";";
		List<Method> methods = new LinkedList<Method>();
		boolean lockOwner = false;

		try 
		{
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID metID = UUID.fromString(dataSet.getString("met_id"));
				Method met = getByID(metID);
				methods.add(met);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		} 
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return methods;
	}
	
	@Override
	public List<Method> getMethodsByFilters(String search, DateRange dtRange) throws DAOException 
	{
		String query = "SELECT met_id FROM " + TABLE_NAME;
		List<Method> methods = new LinkedList<Method>();
		boolean lockOwner = false;

		try 
		{
			query += MySqlDAOFactory.setUpFilters(ComponentType.METHOD, search, dtRange);
			
			lockOwner = MySqlDAOFactory.lockConnection.tryLock();
			Statement statement = MySqlDAOFactory.getInstance().createStatement();
			ResultSet dataSet = statement.executeQuery(query);
			
			while(dataSet.next())
			{
				UUID metID = UUID.fromString(dataSet.getString("met_id"));
				Method met = getByID(metID);
				methods.add(met);
			}
			
		}
		catch (DAOException e) 
		{
			throw e;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("The getAll method failed due to some Query error: " + e.getMessage(), e);
		} 
		finally
		{
			if(lockOwner)
			{
				MySqlDAOFactory.lockConnection.unlock();
				MySqlDAOFactory.getInstance().closeConnection();
			}
		}
		
		return methods;
	}
	
	@Override
	public Method getByID(UUID id) throws DAOException
	{
		Method met = null;
		if(id != null)
		{
			String query = "SELECT * FROM " + TABLE_NAME + " WHERE met_id = '" + id.toString() + "';";
			boolean lockOwner = false;
			
			try 
			{
				Statement statement = MySqlDAOFactory.getInstance().createStatement();
				ResultSet dataSet = statement.executeQuery(query);
				lockOwner = MySqlDAOFactory.lockConnection.tryLock();
				
				if(dataSet.next())
				{
					String name = dataSet.getString("met_name");
					String reliability = MySqlDAOFactory.getEnumFromDatabase(MySqlDAOFactory.RELIABILITY_TYPE, dataSet.getString("met_reliability"));
					ReliabilityType reliabilityType = ReliabilityType.valueOf(reliability);
					String type = MySqlDAOFactory.getEnumFromDatabase(MySqlDAOFactory.METHOD_TYPE, dataSet.getString("met_type"));
					MethodType methodType = MethodType.valueOf(type);
					Date createdDate = new Date(dataSet.getTimestamp("met_createdDate").getTime());
					Date updatedDate = new Date(dataSet.getTimestamp("met_lastUpdated").getTime());
					
					if(methodType == MethodType.CALCULATION)
					{
						String  getRelatedDataQuery = "SELECT * FROM tableName WHERE met_id = '" + id + "';";
						getRelatedDataQuery = getRelatedDataQuery.replace("tableName", CalculationMethod.class.getSimpleName());
						statement = MySqlDAOFactory.getInstance().createStatement();
						ResultSet relatedData = statement.executeQuery(getRelatedDataQuery);
						
						if(relatedData.next())
						{
							Formula formula = MySqlDAOFactory.getInstance().getFormulaDAO().getByID(UUID.fromString(relatedData.getString("for_id")));
							String derMeasureQuery = "SELECT crt_id FROM derivedMeasure WHERE calMet_id = '" + id + "';";	
							ResultSet derivedMeasureData = MySqlDAOFactory.getInstance().createStatement().executeQuery(derMeasureQuery);
							LinkedSet<Criterion> derivedMeasures = new LinkedSet<Criterion>();
							while(derivedMeasureData.next())
							{
								Criterion derivedMeasure = MySqlDAOFactory.getInstance().getCriterionDAO().getByID(UUID.fromString(derivedMeasureData.getString("crt_id")));
								derivedMeasures.add(derivedMeasure);
							}
								
							met = new CalculationMethod(id, formula, derivedMeasures);
						}
						else
							met = new CalculationMethod(id, name);
					}
					else
						met = new Method(id, methodType);
					
					met.setName(name);
					met.setReliability(reliabilityType);
					met.setCreatedDate(createdDate);
					met.setLastUpdated(updatedDate);
					
					if(dataSet.next())
						System.err.println("WARING: Two or more " + TABLE_NAME + " with the same ID were found in the database. met_id = " + id);
				}
				else
					throw new DAOException("The " + TABLE_NAME + " identified by the id " + id + " hasn't been found in the database.");
			}
			catch (DAOException e) 
			{
				throw e;
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
				throw new DAOException("The getByID method failed due to some Query error: " + e.getMessage(), e);
			}
			catch (FormulaException e) 
			{
				e.printStackTrace();
				throw new DAOException("The getAll method failed due to some Formula error: " + e.getMessage(), e);
			}
			finally
			{
				if(lockOwner)
				{
					MySqlDAOFactory.lockConnection.unlock();
					MySqlDAOFactory.getInstance().closeConnection();
				}
			}
		}
		
		return met;
	}
	
	private LinkedSet<UUID> getRelatedDerivedMeasuresID(UUID calMetID) throws DAOException, SQLException
	{
		LinkedSet<UUID> relatedIDs = new LinkedSet<UUID>();
		String query = "SELECT crt_id FROM derivedmeasure WHERE calMet_id = '" + calMetID + "';";
		
		Statement statement = MySqlDAOFactory.getInstance().createStatement();
		ResultSet dataSet = statement.executeQuery(query);
		
		while(dataSet.next())
		{
			String crtID = dataSet.getString("crt_id");
			relatedIDs.add(UUID.fromString(crtID));
		}
		
		return relatedIDs;
	}
	
	private void addDerivedMeasure(UUID calMetID, UUID derMeasureID) throws DAOException, SQLException
	{
		String query = "INSERT INTO derivedMeasure(calMet_id, crt_id) VALUES(?, ?)";
		
		PreparedStatement preStatement = MySqlDAOFactory.getInstance().prepareStatement(query);
		preStatement.setString(1, calMetID.toString());
		preStatement.setString(2, derMeasureID.toString());
		preStatement.executeUpdate();
	}
	
	private LinkedSet<UUID> findOldMeasures(LinkedSet<UUID> relatedMeasures, LinkedSet<Criterion> metMeasures)
	{
		LinkedSet<UUID> toRemoveMeasures = new LinkedSet<UUID>();
		
		for(UUID measureID : relatedMeasures)
		{
			boolean contains = false;
			for(Criterion derMeasure : metMeasures)
			{
				if(derMeasure.getID().equals(measureID))
				{
					contains = true;
					break;
				}
			}
			
			if(!contains)
				toRemoveMeasures.add(measureID);
		}
					
		return toRemoveMeasures;
	}

	private void removeDerivedMeasure(UUID derMeasureID) throws DAOException, SQLException
	{
		String query = "DELETE FROM derivedMeasure WHERE crt_id = '" + derMeasureID + "';";

		Statement statement = MySqlDAOFactory.getInstance().createStatement();
		statement.executeUpdate(query);
	}
}